package com.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.core.dao.UsernameDAO;
import com.core.database.Manager;
import com.core.entities.Username;
import com.core.exception.DAOException;

public class UsernameDAOImpl implements UsernameDAO {
	Username username = new Username();
	Connection connection = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	ArrayList<Username> usernamesList = new ArrayList<Username>();

	private static UsernameDAOImpl instance = null;

	public static UsernameDAOImpl getInstance() {
		if (instance == null) {
			instance = new UsernameDAOImpl();
		}
		return instance;
	}

	@Override
	public void insert(Username o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "INSERT INTO username (username, password, role) VALUE (?, ?, ?)";
			ps = connection.prepareStatement(sql);
			ps.setString(1, o.getUsername());
			ps.setString(2, o.getPassword());
			ps.setString(3, o.getRole());

			int result = ps.executeUpdate();
			if (result != 1) {
				throw new SQLException("No row inserted in Username table!");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new RuntimeException("cannot add new username", ex);
		} finally {
			Manager.closeConnection(connection);
		}

	}

	@Override
	public Username findById(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM username WHERE id = '" + id + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					username.setId(rs.getInt(1));
					username.setUsername(rs.getString(2));
					username.setPassword(rs.getString(3));
					username.setRole(rs.getString(4));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return username;
	}

	@Override
	public List<Username> findAll() throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM username ";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					username.setId(rs.getInt(1));
					username.setUsername(rs.getString(2));
					username.setPassword(rs.getString(3));
					username.setRole(rs.getString(4));
					usernamesList.add(username);
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return usernamesList;
	}

	@Override
	public void delete(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " DELETE FROM username WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate();
			if (result != 1) {
				ps.setString(1, username.getUsername());
				ps.setString(2, username.getPassword());
				ps.setString(3, username.getRole());
			}

		} catch (SQLException s) {
			s.printStackTrace();

		}
	}

	@Override
	public void update(Username o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "UPDATE username SET username='" + o.getUsername()
					+ "', password = '" + o.getPassword() + "', role ='"
					+ o.getRole() + "' WHERE id = " + o.getId();
			ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate(sql);

			if (result != 1) {
				ps.setString(1, o.getUsername());
				ps.setString(2, o.getPassword());
				ps.setString(3, o.getRole());

			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
	}

	@Override
	public Username findByUsername(String _username) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM username WHERE username = '" + _username
					+ "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					username.setId(rs.getInt(1));
					username.setUsername(rs.getString(2));
					username.setPassword(rs.getString(3));
					username.setRole(rs.getString(4));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return username;
	}

	@Override
	public Username findByPassword(String password) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM username WHERE password = '" + password
					+ "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					username.setId(rs.getInt(1));
					username.setUsername(rs.getString(2));
					username.setPassword(rs.getString(3));
					username.setRole(rs.getString(4));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return username;
	}

	@Override
	public Username findByRole(String role) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM username WHERE role = '" + role + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					username.setId(rs.getInt(1));
					username.setUsername(rs.getString(2));
					username.setPassword(rs.getString(3));
					username.setRole(rs.getString(4));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return username;
	}

}
